package cn.dida.dao;

import java.util.List;
import java.util.Map;
import cn.dida.model.LgsLogistics;
import cn.dida.model.LgsRelate;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import cn.dida.model.Pageable;

@Repository
public class LgsDao extends DaoBase {

	/**
	 * 批次查询
	 * @param companyId
	 * @param batchNub
	 * @param page
	 * @return
	 */
	public Pageable<Map<String,Object>> queryLgsInfoByBatch(Integer companyId, String batchNub, Integer page){
		Object[] args = {};
		StringBuffer sqlQueryResultCount = new StringBuffer("select count(*) from ( SELECT COUNT(1) FROM lgs_log_pda_scan lgs  " +
				"join lgs_bills b on b.ID=lgs.bills_id  left join product p on p.id = lgs.product_id " +
				"left join company c on c.id = lgs.scan_company_id left join lgs_depot d on d.ID = lgs.depot_id left join lgs_node n on n.ID = lgs.node_id , " +
				"(select lo.ID from lgs_log_pda_scan lo,(select max(scan_time) maxTime ,qrcode_box_id from lgs_log_pda_scan  group by qrcode_box_id ) lgs1  where lo.scan_time=lgs1.maxTime and lo.qrcode_box_id=lgs1.qrcode_box_id) lgs2 " );
//				" where lgs.ID = lgs2.ID  and lgs.batch_nub = ? group  by lgs.bills_id, lgs.product_id ");
		StringBuffer sqlQueryResult = new StringBuffer("SELECT lgs.batch_nub,lgs.scan_time, p.name 'proName', c.name 'scanCompanyName', " +
				"d.name 'depotName', n.name 'nodeName' , n.type 'nodeType' , count(*) 'nubs', (ceil(count(*)*pb.pcount)) ' botNums', " +
				"(select d.cust_name from lgs_bd_cust d where d.id = b.depot_id_from) as 'from', " +
				"(select d.cust_name from lgs_bd_cust d where d.id = b.depot_id_to) as 'to' " +
				"from lgs_log_pda_scan lgs  " +
				"join lgs_bills b on b.ID=lgs.bills_id  left join product p on p.id = lgs.product_id left join pbc pb on pb.id=p.pbc_id " +
				"left join company c on c.id = lgs.scan_company_id left join lgs_depot d on d.ID = lgs.depot_id " +
				"left join lgs_node n on n.ID = lgs.node_id , " +
				"(select lo.ID from lgs_log_pda_scan lo,(select max(scan_time) maxTime ,qrcode_box_id from lgs_log_pda_scan  group by qrcode_box_id ) lgs1  where lo.scan_time=lgs1.maxTime and lo.qrcode_box_id=lgs1.qrcode_box_id) lgs2" );
//				" where lgs.ID = lgs2.ID  and lgs.company_id = ? group  by lgs.bills_id, lgs.product_id");

		StringBuffer sqlWhere = new StringBuffer(" where  lgs.ID = lgs2.ID and lgs.company_id = ?  and lgs.batch_nub = ?  ");
		args = ArrayUtils.add(args, companyId);
		args = ArrayUtils.add(args, batchNub);

//		if(StringUtils.isNotBlank(batchNub)){
//        	sqlWhere.append(" and lgs.batch_nub = ?  ");
//        	args = ArrayUtils.add(args, batchNub);
//        }

        sqlWhere.append(" group  by lgs.bills_id, lgs.product_id");
		// 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere + ") a").toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  ORDER BY lgs.scan_time desc  limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
	}

	/**
	 * 批次查询 数据导出
	 * @param companyId
	 * @param batchNub
	 * @return
	 */
	public List<Map<String,Object>> batchExport(Integer companyId, String batchNub){
		Object[] args = {};
		StringBuffer sqlQueryResult = new StringBuffer("SELECT lgs.batch_nub,lgs.scan_time, p.name 'proName', c.name 'scanCompanyName', d.name 'depotName', " +
				"n.name 'nodeName' , n.type 'nodeType' , count(*) 'nubs', (ceil(count(*)*pb.pcount)) ' botNums', " +
				"(select d.cust_name from lgs_bd_cust d where d.id = b.depot_id_from) as 'from', " +
				"(select d.cust_name from lgs_bd_cust d where d.id = b.depot_id_to) as 'to' " +
				"from lgs_log_pda_scan lgs  " +
				"join lgs_bills b on b.ID=lgs.bills_id  left join product p on p.id = lgs.product_id left join pbc pb on pb.id=p.pbc_id " +
				"left join company c on c.id = lgs.scan_company_id left join lgs_depot d on d.ID = lgs.depot_id " +
				"left join lgs_node n on n.ID = lgs.node_id , " +
				"(select lo.ID from lgs_log_pda_scan lo,(select max(scan_time) maxTime ,qrcode_box_id from lgs_log_pda_scan  group by qrcode_box_id ) lgs1  where lo.scan_time=lgs1.maxTime and lo.qrcode_box_id=lgs1.qrcode_box_id) lgs2" );

		StringBuffer sqlWhere = new StringBuffer("  where  lgs.ID = lgs2.ID and lgs.company_id = ? and lgs.batch_nub =  ? ") ;
		args = ArrayUtils.add(args, companyId);
		args = ArrayUtils.add(args, batchNub);

		sqlWhere.append(" group  by lgs.bills_id, lgs.product_id ORDER BY lgs.scan_time desc");

		List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.append(sqlWhere).toString(), args);
		return result;
	}



	/**
	 * 物流查询
	 * @param companyId
	 * @param boxNo
	 * @return
	 */
	public List<Map<String,Object>> getLogistics(Integer companyId, String boxNo) {
		String sql = "select sc.bills_id 'billsId',sc.scan_time 'scanTime',de.name 'scanDepotName',bi.bills_no 'billsNo', bi.type 'billsType'," +
				" no.name 'nodeName',no.type 'nodeType',pr.name 'proName', sc.batch_nub 'batchNub', " +
				"(select d.cust_name from lgs_bd_cust d where d.id = bi.depot_id_from) as 'fromDepot', " +
				"(select d.cust_name from lgs_bd_cust d where d.id = bi.depot_id_to) as 'toDeot' "+
				" from lgs_log_pda_scan sc LEFT join product pr on pr.id=sc.product_id" +
				" join lgs_depot de on de.ID=sc.depot_id join lgs_bills bi on bi.ID=sc.bills_id  LEFT join lgs_node no on  no.ID=bi.node_id " +
				" where sc.company_id = ? and sc.qrcode_box_id = ? and bi.sta != 5 ORDER BY sc.scan_time DESC ";

		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, companyId, boxNo);
		return list;
	}

	/**
	 * 获取进货网点最后扫描记录
	 * @param companyId
	 * @param boxNo
	 * @return
	 */
	public List<Map<String,Object>> getCustScan(Integer companyId, String boxNo){

		String sql ="select 1 'cust', cu.cust_code 'custCode', cu.cust_name 'custName',sc.scan_time 'scanTime' from lgs_log_cust_scan sc " +
				"join lgs_bd_cust cu on cu.id = sc.branch_id where sc.company_id=? and box_id=? " +
				"order by sc.scan_time desc ";

		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, companyId, boxNo);

		return list;

	}
	/**
	 * 根据瓶码获取箱码
	 * @param pQrcodeId
	 * @return
	 */
	public String  getBoxByBottle(String pQrcodeId,Integer companyId) {
		String sql = "SELECT res.id, res.contact_way 'contactWay', res.p_qrcode_id 'pQrcodeId'," +
				" res.b_qrcode_id 'bQrcodeId', res.tray_qrcode 'trayQrcode', res.contact_type 'contactType', " +
				"res.ts  from res_qrbind res where res.dr!=1 and res.contact_type =1 and res.company_id=? and res.p_qrcode_id = ?";

		List<LgsRelate> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LgsRelate>(LgsRelate.class),companyId, pQrcodeId);
		if (null != list && list.size() == 1) {
			return list.get(0).getbQrcodeId() + "";
		}
		return null;
	}

	/**
	 * 根据箱码获取瓶码
	 */
	public List<LgsRelate> getBottleByBox(String bQrcodeId,Integer companyId) {
		String sql = "select  res.id, res.contact_way 'contactWay', res.p_qrcode_id 'pQrcodeId',res.b_qrcode_id 'bQrcodeId'," +
				" res.ts , res.contact_type 'contactType', pda.real_name 'cAdmin',dep.name 'depotName',pr.name 'proName'" +
				"  from res_qrbind res left join lgs_depot dep on dep.id = res.depot left join lgs_pda_user pda on pda.id=res.c_admin  " +
				" LEFT JOIN  qrcode_detail_"+companyId +" qr on qr.id = res.b_qrcode_id LEFT JOIN  product pr on pr.id=qr.product_id"+
				" where res.dr!=1 and res.company_id=? and res.contact_type = 1 and res.b_qrcode_id = ? ";

		List<LgsRelate> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LgsRelate.class),companyId, bQrcodeId);
		return list;
	}

	/**
	 * 根据托盘码获取包含的产品的所属公司集合
	 * @param trayQrcode
	 * @param bindCompanyId
	 * @return
	 */
	public List<Integer> getTrayCompany(String trayQrcode,Integer bindCompanyId) {
		String sql = "select r.company_id from res_qrbind r where r.contact_type=2" +
				" and r.bind_company_id=? and r.tray_qrcode=? " +
				" group by r.company_id";

		List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class,bindCompanyId, trayQrcode);
		return list;
	}

	/**
	 * 根据托盘码获取箱码
	 * @param trayQrcode
	 * @param companyId
	 * @return
	 */
	public List<LgsRelate> getBoxByTray(String trayQrcode,Integer companyId,Integer bindCompanyId) {
		String sql = "select  res.id, res.contact_way 'contactWay', res.p_qrcode_id 'pQrcodeId',res.b_qrcode_id 'bQrcodeId'," +
				" res.ts , res.contact_type 'contactType',res.company_id 'companyId', pda.real_name 'cAdmin',dep.name 'depotName'," +
				"pr.name 'proName',c.name 'bingCompanyName'" +
				"  from res_qrbind res left join lgs_depot dep on dep.id = res.depot left join lgs_pda_user pda on pda.id=res.c_admin  " +
				" LEFT JOIN  qrcode_detail_"+companyId +" qr on qr.id = res.b_qrcode_id LEFT JOIN  product pr on pr.id=qr.product_id " +
				" left join company c on c.id=res.bind_company_id "+
				" where res.dr!=1 and res.company_id=? and res.bind_company_id=? and res.contact_type = 2 and res.tray_qrcode = ?  ";

		List<LgsRelate> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LgsRelate.class),companyId,bindCompanyId, trayQrcode);
		return list;
	}

	/**
	 * 关联管理 瓶箱关联
	 * @param cTimeS 操作开始时间
	 * @param cTimeE 操作结束时间
	 * @param depot 操作地点id
	 * @param cAdmin 操作人id
	 * @param page
	 * @return
	 */
	public Pageable<LgsRelate> getRelateBox(Integer companyId,String cTimeS, String cTimeE, Integer depot, String cAdmin,Integer page){

		Object[] args = {};
		StringBuffer sqlQueryResultCount = new StringBuffer("select count(*) from(select count(1) from res_qrbind res left join lgs_pda_user pda on pda.id=res.c_admin join company c on c.id = res.bind_company_id ");
		StringBuffer sqlQueryResult = new StringBuffer("select res.id, res.company_id 'companyId',c.name 'bingCompanyName', " +
				" res.b_qrcode_id 'bQrcodeId', res.ts, dep.name 'depotName',pda.real_name 'cAdmin', count(1) 'boxNum' " +
				" from res_qrbind res left join lgs_depot dep on dep.id = res.depot left join lgs_pda_user pda on pda.id=res.c_admin " +
				"join company c on c.id = res.bind_company_id");

		StringBuffer sqlWhere = new StringBuffer(" where res.dr != 1 and res.qrcode_type =1 and res.contact_type =1 and (res.company_id = ? || bind_company_id = ?)") ;
		args = ArrayUtils.add(args, companyId);
		args = ArrayUtils.add(args, companyId);

		if(depot != null){
			sqlWhere.append(" and res.depot = ? ");
			args = ArrayUtils.add(args, depot);
		}
		if(cAdmin != null && cAdmin != ""){
			sqlWhere.append(" and pda.real_name like ? ");
			args = ArrayUtils.add(args, "%" + cAdmin +"%");
		}

		if(StringUtils.isNotBlank(cTimeS)){
			sqlWhere.append(" and res.ts >= ?");
			args = ArrayUtils.add(args, Long.parseLong(cTimeS));
		}
		if(StringUtils.isNotBlank(cTimeE)){
			sqlWhere.append(" and res.ts <= ? ");
			args = ArrayUtils.add(args, Long.parseLong(cTimeE));
		}
		sqlWhere.append(" group by res.b_qrcode_id");

		// 查询记录总数
	 	Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere+") a").toString(), Integer.class, args);
		sqlQueryResult.append(sqlWhere).append("  order by res.ts desc limit ?,?");
		args = ArrayUtils.add(args, (page - 1) * getPageSize());
		args = ArrayUtils.add(args, getPageSize());
		List<LgsRelate> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsRelate.class));
		return new Pageable<>(page, getPageSize(), resultTotal, result);

	}

	/**
	 * 关联管理 导出瓶箱关联
	 * @param cTimeS 操作开始时间
	 * @param cTimeE 操作结束时间
	 * @param depot 操作地点id
	 * @param cAdmin 操作人id
	 * @return
	 */
	public List<LgsRelate> relateBoxExport(Integer companyId,String cTimeS, String cTimeE, Integer depot, String cAdmin){

		Object[] args = {};
		StringBuffer sqlQueryResult = new StringBuffer("select res.id, res.company_id 'companyId',c.name 'bingCompanyName'," +
				" res.b_qrcode_id 'bQrcodeId', res.ts, dep.name 'depotName',pda.real_name 'cAdmin', count(1) 'boxNum'" +
				" from res_qrbind res left join lgs_depot dep on dep.id = res.depot left join lgs_pda_user pda on pda.id=res.c_admin " +
				" join company c on c.id = res.bind_company_id" );

		StringBuffer sqlWhere = new StringBuffer(" where res.dr != 1 and res.qrcode_type =1 and res.contact_type =1 and (res.company_id = ? || bind_company_id = ?)  ") ;
		args = ArrayUtils.add(args, companyId);
		args = ArrayUtils.add(args, companyId);

		if(depot != null){
			sqlWhere.append(" and res.depot = ? ");
			args = ArrayUtils.add(args, depot);
		}
		if(cAdmin != null  && cAdmin != ""){
			sqlWhere.append(" and pda.real_name like ? ");
			args = ArrayUtils.add(args, "%" + cAdmin +"%");
		}

		if(StringUtils.isNotBlank(cTimeS)){
			sqlWhere.append(" and res.ts >= ?");
			args = ArrayUtils.add(args, Long.parseLong(cTimeS));
		}
		if(StringUtils.isNotBlank(cTimeE)){
			sqlWhere.append(" and res.ts <= ? ");
			args = ArrayUtils.add(args, Long.parseLong(cTimeE));
		}
		sqlWhere.append(" group by res.b_qrcode_id");
		sqlQueryResult.append(sqlWhere).append("  order by res.ts desc ");
		List<LgsRelate> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsRelate.class));
		return result;

	}


	/**
	 * 关联管理  托盘关联
	 * @param companyId
	 * @param cTimeS
	 * @param cTimeE
	 * @param depot
	 * @param cAdmin
	 * @param page
	 * @return
	 */
	public Pageable<LgsRelate> getRelateTray(Integer companyId,String cTimeS, String cTimeE, Integer depot, String cAdmin,Integer page){


		Object[] args = {};
		StringBuffer sqlQueryResultCount = new StringBuffer("select count(*) from(select count(1) from res_qrbind res left join lgs_pda_user pda on pda.id=res.c_admin ");
		StringBuffer sqlQueryResult = new StringBuffer("select res.id," +
				" res.tray_qrcode 'trayQrcode', res.ts, dep.name 'depotName',pda.real_name 'cAdmin',  count(1) 'boxNum' " +
				" from res_qrbind res left join lgs_depot dep on dep.id = res.depot left join lgs_pda_user pda on pda.id=res.c_admin " );

		StringBuffer sqlWhere = new StringBuffer(" where res.dr != 1 and res.contact_type =2 and  bind_company_id = ? ") ;
		args = ArrayUtils.add(args, companyId);
		if(depot != null){
			sqlWhere.append(" and res.depot = ? ");
			args = ArrayUtils.add(args, depot);
		}
		if(cAdmin != null  && cAdmin != ""){
			sqlWhere.append(" and pda.real_name like ? ");
			args = ArrayUtils.add(args, "%" + cAdmin +"%");
		}

		if(StringUtils.isNotBlank(cTimeS)){
			sqlWhere.append(" and res.ts >= ?");
			args = ArrayUtils.add(args, Long.parseLong(cTimeS));
		}
		if(StringUtils.isNotBlank(cTimeE)){
			sqlWhere.append(" and res.ts <= ? ");
			args = ArrayUtils.add(args, Long.parseLong(cTimeE));
		}

		sqlWhere.append(" group by res.tray_qrcode");

		// 查询记录总数
		Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere+") a").toString(), Integer.class, args);
		// 查询记录
		sqlQueryResult.append(sqlWhere).append("  order by res.ts desc limit ?,?");
		args = ArrayUtils.add(args, (page - 1) * getPageSize());
		args = ArrayUtils.add(args, getPageSize());
		List<LgsRelate> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsRelate.class));
		return new Pageable<>(page, getPageSize(), resultTotal, result);

	}


	/**
	 * 关联管理 导出托盘关联
	 * @param cTimeS 操作开始时间
	 * @param cTimeE 操作结束时间
	 * @param depot 操作地点id
	 * @param cAdmin 操作人id
	 * @return
	 */
	public List<LgsRelate> relateTrayExport(Integer companyId,String cTimeS, String cTimeE, Integer depot, String cAdmin){
		Object[] args = {};
		StringBuffer sqlQueryResult = new StringBuffer("select res.id," +
				" res.tray_qrcode 'trayQrcode', res.ts, dep.name 'depotName',pda.real_name 'cAdmin',  count(1) 'boxNum' " +
				" from res_qrbind res left join lgs_depot dep on dep.id = res.depot left join lgs_pda_user pda on pda.id=res.c_admin " );

		StringBuffer sqlWhere = new StringBuffer(" where res.dr != 1 and res.contact_type =2 and res.bind_company_id = ? ") ;
		args = ArrayUtils.add(args, companyId);

		if(depot != null){
			sqlWhere.append(" and res.depot = ? ");
			args = ArrayUtils.add(args, depot);
		}
		if(cAdmin != null  && cAdmin != ""){
			sqlWhere.append(" and pda.real_name like ? ");
			args = ArrayUtils.add(args, "%" + cAdmin +"%");
		}

		if(StringUtils.isNotBlank(cTimeS)){
			sqlWhere.append(" and res.ts >= ?");
			args = ArrayUtils.add(args, Long.parseLong(cTimeS));
		}
		if(StringUtils.isNotBlank(cTimeE)){
			sqlWhere.append(" and res.ts <= ? ");
			args = ArrayUtils.add(args, Long.parseLong(cTimeE));
		}
		sqlWhere.append(" group by res.tray_qrcode order by res.ts desc");

		List<LgsRelate> result = jdbcTemplate.query(sqlQueryResult.append(sqlWhere).toString(), args, new BeanPropertyRowMapper<>(LgsRelate.class));
		return result;
	}
}
